/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package StudentFeesDetail;

import FeeDeposit.FeeDepositBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Vector;
/**
 *
 * @author aamod
 */
public class Query {
   static Connection con=null;
   
   public static boolean countFeeDetails(String uniq) throws SQLException
   {
       boolean val=false;
       try
       {
          con=DBConnection.Connection.getConnection(); 
          PreparedStatement pre=con.prepareStatement("select count(*) from student_fees_detail where UNIQUE_FEES_DETAIL like '%"+uniq+"'");
          ResultSet rs=pre.executeQuery();
          while(rs.next())
          {
              if(rs.getInt(1)==0)
              {
                  val=false;
              }
              else
              {
                  val=true;
              }
          }
       }
       catch(Exception e)
       {
           return val;
       }
       finally
       {
           con.close();
       }
       return val;
   }
   public static int add(Vector list)throws SQLException
   {
       int i=0;
       int k[]=null;
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("insert into STUDENT_FEES_DETAIL values(STUDENTFEESDETAILID.nextval,?,?,?,?,?,?,?,?,?,?,sysdate,?,?)");
           Iterator it=list.iterator();
           while(it.hasNext())
           {
           StudentFeeDetailsBean sfb=(StudentFeeDetailsBean)it.next();
           pre.setInt(1, sfb.getSubFeeTypeId());
           pre.setFloat(2, sfb.getAmount());
           pre.setFloat(3, sfb.getAdditionalAmount());
           pre.setFloat(4, sfb.getConcessionAmount());
           pre.setFloat(5, sfb.getTotalAmount());
           pre.setInt(6, sfb.getAdmissionId());
           pre.setInt(7, sfb.getSemester());
           pre.setInt(8, sfb.getCategoryId());
           pre.setString(9, sfb.getUniqueFeeDetail());
           pre.setInt(10, sfb.getUserId());
           pre.setFloat(11, sfb.getAmountPaid());
           pre.setInt(12, sfb.getFeeTypeId());
           pre.addBatch();
           }
           k=pre.executeBatch();
           if(k.length>0)
           {
               i=1;
           }
           else
           {
               i=0;
           }
       }catch(Exception e){
           System.out.println(e);
       }finally{
           con.close();
       }return i;
   }
   
   public static float totalFees(String code) throws SQLException
   {
       float sum=0f;
       try
       {
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select sum(amount) from fees_deposit where ADMISSION_ID||'-'||SEMESTER||'-'||STUD_SESSION||'-'||FEE_TYPE_ID=?");
           pre.setString(1,code);
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               sum=rs.getFloat(1);
           }
       }
       catch(Exception e)
       {
          return sum; 
       }
       finally
       {
           con.close();
       }
      return sum;
   }
   public static int delete(String uniq) throws SQLException
   {
       int i=0;
       try
       {
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("delete from student_fees_detail where UNIQUE_FEES_DETAIL like '%"+uniq+"'");
           i=pre.executeUpdate();
       }
       catch(Exception e)
       {
           return i;
       }
       finally
       {
           con.close();
       }
       return i;
   }
   
   public static Vector veiwAll(String code) throws SQLException
   {
       Vector<Object> list=new Vector<Object>();
       try
       {
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from student_fees_detail where UNIQUE_FEES_DETAIL like '%"+code+"'");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               StudentFeeDetailsBean sfb=new StudentFeeDetailsBean();
               sfb.setStudentFeesDetialId(rs.getInt(1));
               sfb.setSubFeeTypeId(rs.getInt(2));
               sfb.setAmount(rs.getFloat(3));
               sfb.setAdditionalAmount(rs.getFloat(4));
               sfb.setConcessionAmount(rs.getFloat(5));
               sfb.setTotalAmount(rs.getFloat(6));
               sfb.setAdmissionId(rs.getInt(7));
               sfb.setSemester(rs.getInt(8));
               sfb.setCategoryId(rs.getInt(9));
               sfb.setUniqueFeeDetail(rs.getString(10));
               sfb.setUserId(rs.getInt(11));
               sfb.setEntryDate(rs.getDate(12));
               sfb.setAmountPaid(rs.getFloat(13));
               sfb.setFeeTypeId(rs.getInt(14));
               list.add(sfb);
           }
       }
       catch(Exception e)
       {
           return list;
       }
       finally
       {
           con.close();
       }
       return list;
   }
}
